using System;
using System.Collections.Generic;
using System.Linq;
using Dapper;
using MySql.Data.MySqlClient;

namespace LibraryManagementSystem
{
    /// <summary>
    /// 图书管理服务类
    /// </summary>
    public class BookService
    {
        #region 图书分类管理

        /// <summary>
        /// 获取所有图书分类
        /// </summary>
        public List<BookCategory> GetAllCategories()
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = "SELECT * FROM BookCategories ORDER BY CategoryCode";
                return connection.Query<BookCategory>(sql).ToList();
            }
        }

        /// <summary>
        /// 获取分类树形结构
        /// </summary>
        public List<BookCategory> GetCategoryTree()
        {
            var allCategories = GetAllCategories();
            var rootCategories = allCategories.Where(c => c.ParentId == null).ToList();

            // 递归构建树形结构
            foreach (var root in rootCategories)
            {
                BuildCategoryTree(root, allCategories);
            }

            return rootCategories;
        }

        private void BuildCategoryTree(BookCategory parent, List<BookCategory> allCategories)
        {
            parent.Children = allCategories.Where(c => c.ParentId == parent.Id).ToList();
            foreach (var child in parent.Children)
            {
                BuildCategoryTree(child, allCategories);
            }
        }

        /// <summary>
        /// 添加图书分类
        /// </summary>
        public bool AddCategory(BookCategory category)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                INSERT INTO BookCategories (CategoryCode, CategoryName, ParentId, Description)
                VALUES (@CategoryCode, @CategoryName, @ParentId, @Description);
                SELECT LAST_INSERT_ID();";

                var id = connection.ExecuteScalar<int>(sql, category);
                return id > 0;
            }
        }

        /// <summary>
        /// 更新图书分类
        /// </summary>
        public bool UpdateCategory(BookCategory category)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                UPDATE BookCategories
                SET CategoryCode = @CategoryCode,
                    CategoryName = @CategoryName,
                    ParentId = @ParentId,
                    Description = @Description
                WHERE Id = @Id";

                var result = connection.Execute(sql, category);
                return result > 0;
            }
        }

        /// <summary>
        /// 删除图书分类
        /// </summary>
        public bool DeleteCategory(int categoryId)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                // 检查分类下是否有图书
                string checkBooks = "SELECT COUNT(*) FROM Books WHERE CategoryId = @CategoryId";
                int bookCount = connection.ExecuteScalar<int>(checkBooks, new { CategoryId = categoryId });
                if (bookCount > 0)
                {
                    return false; // 分类下有图书，不能删除
                }

                // 检查是否有子分类
                string checkChildren = "SELECT COUNT(*) FROM BookCategories WHERE ParentId = @ParentId";
                int childCount = connection.ExecuteScalar<int>(checkChildren, new { ParentId = categoryId });
                if (childCount > 0)
                {
                    return false; // 有子分类，不能删除
                }

                string sql = "DELETE FROM BookCategories WHERE Id = @Id";
                var result = connection.Execute(sql, new { Id = categoryId });
                return result > 0;
            }
        }

        #endregion

        #region 图书管理

        /// <summary>
        /// 获取所有图书
        /// </summary>
        public List<Book> GetAllBooks()
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                SELECT b.*, c.CategoryName, c.CategoryCode, c.Description as CategoryDescription
                FROM Books b
                LEFT JOIN BookCategories c ON b.CategoryId = c.Id
                ORDER BY b.Title";

                var books = new List<Book>();
                using (var reader = connection.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        var book = new Book
                        {
                            Id = Convert.ToInt32(reader["Id"]),
                            ISBN = reader["ISBN"].ToString(),
                            Title = reader["Title"].ToString(),
                            Author = reader["Author"].ToString(),
                            Publisher = reader["Publisher"].ToString(),
                            PublishDate = reader["PublishDate"] == DBNull.Value ? null : (DateTime?)reader["PublishDate"],
                            CategoryId = reader["CategoryId"] == DBNull.Value ? 0 : Convert.ToInt32(reader["CategoryId"]),
                            Price = reader["Price"] == DBNull.Value ? null : (decimal?)reader["Price"],
                            Pages = reader["Pages"] == DBNull.Value ? null : (int?)Convert.ToInt32(reader["Pages"]),
                            Description = reader["Description"] == DBNull.Value ? null : reader["Description"].ToString(),
                            CoverImage = reader["CoverImage"] == DBNull.Value ? null : reader["CoverImage"].ToString(),
                            Location = reader["Location"] == DBNull.Value ? null : reader["Location"].ToString(),
                            Status = reader["Status"] == DBNull.Value ? null : reader["Status"].ToString(),
                            CreatedAt = Convert.ToDateTime(reader["CreatedAt"])
                        };

                        if (reader["CategoryId"] != DBNull.Value)
                        {
                            book.Category = new BookCategory
                            {
                                Id = Convert.ToInt32(reader["CategoryId"]),
                                CategoryName = reader["CategoryName"] == DBNull.Value ? null : reader["CategoryName"].ToString(),
                                CategoryCode = reader["CategoryCode"] == DBNull.Value ? null : reader["CategoryCode"].ToString(),
                                Description = reader["CategoryDescription"] == DBNull.Value ? null : reader["CategoryDescription"].ToString()
                            };
                        }

                        books.Add(book);
                    }
                }

                // 获取所有图书的副本信息
                foreach (var book in books)
                {
                    book.Items = GetBookItems(book.Id);
                }

                return books;
            }
        }

        /// <summary>
        /// 根据ISBN获取图书
        /// </summary>
        public Book GetBookByISBN(string isbn)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                SELECT b.*, c.CategoryName
                FROM Books b
                LEFT JOIN BookCategories c ON b.CategoryId = c.Id
                WHERE b.ISBN = @ISBN";

                var book = connection.Query<Book, BookCategory, Book>(
                    sql,
                    (book, category) =>
                    {
                        book.Category = category;
                        return book;
                    },
                    new { ISBN = isbn },
                    splitOn: "CategoryName"
                ).FirstOrDefault();

                if (book != null)
                {
                    // 获取该图书的所有副本
                    string itemsSql = "SELECT * FROM BookItems WHERE BookId = @BookId";
                    book.Items = connection.Query<BookItem>(itemsSql, new { BookId = book.Id }).ToList();
                }

                return book;
            }
        }

        /// <summary>
        /// 搜索图书
        /// </summary>
        public List<Book> SearchBooks(string keyword, int? categoryId = null)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                SELECT b.*, c.CategoryName
                FROM Books b
                LEFT JOIN BookCategories c ON b.CategoryId = c.Id
                WHERE (b.Title LIKE @Keyword OR b.Author LIKE @Keyword OR b.ISBN LIKE @Keyword)";

                var parameters = new DynamicParameters();
                parameters.Add("@Keyword", $"%{keyword}%");

                if (categoryId.HasValue)
                {
                    sql += " AND b.CategoryId = @CategoryId";
                    parameters.Add("@CategoryId", categoryId.Value);
                }

                sql += " ORDER BY b.Title";

                var bookDict = new Dictionary<int, Book>();

                var books = connection.Query<Book, BookCategory, Book>(
                    sql,
                    (book, category) =>
                    {
                        if (!bookDict.TryGetValue(book.Id, out var bookEntry))
                        {
                            bookEntry = book;
                            bookEntry.Category = category;
                            bookDict.Add(bookEntry.Id, bookEntry);
                        }
                        return bookEntry;
                    },
                    parameters,
                    splitOn: "CategoryName"
                ).Distinct().ToList();

                // 获取图书的副本信息
                foreach (var book in books)
                {
                    book.Items = GetBookItems(book.Id);
                }

                return books;
            }
        }

        /// <summary>
        /// 添加图书
        /// </summary>
        public int AddBook(Book book)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                INSERT INTO Books (ISBN, Title, Author, Publisher, PublishDate, CategoryId, 
                                  Price, Pages, Description, CoverImage, Location, Status, CreatedAt) 
                                  VALUES (@ISBN, @Title, @Author, @Publisher, @PublishDate, @CategoryId, 
                                  @Price, @Pages, @Description, @CoverImage, @Location, @Status, @CreatedAt);
                                  SELECT LAST_INSERT_ID();";

                var parameters = new DynamicParameters();
                parameters.Add("@ISBN", book.ISBN);
                parameters.Add("@Title", book.Title);
                parameters.Add("@Author", book.Author);
                parameters.Add("@Publisher", book.Publisher);
                parameters.Add("@PublishDate", book.PublishDate);
                parameters.Add("@CategoryId", book.CategoryId);
                parameters.Add("@Price", book.Price);
                parameters.Add("@Pages", book.Pages);
                parameters.Add("@Description", book.Description);
                parameters.Add("@CoverImage", book.CoverImage);
                parameters.Add("@Location", book.Location);
                parameters.Add("@Status", book.Status);
                parameters.Add("@CreatedAt", DateTime.Now);

                var id = connection.ExecuteScalar<int>(sql, parameters);
                return id;
            }
        }

        /// <summary>
        /// 更新图书信息
        /// </summary>
        public bool UpdateBook(Book book)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                UPDATE Books
                SET ISBN = @ISBN,
                    Title = @Title,
                    Author = @Author,
                    Publisher = @Publisher,
                    PublishDate = @PublishDate,
                    CategoryId = @CategoryId,
                    Price = @Price,
                    Pages = @Pages,
                    Description = @Description,
                    CoverImage = @CoverImage,
                    Location = @Location,
                    Status = @Status
                WHERE Id = @Id";

                var parameters = new DynamicParameters();
                parameters.Add("@ISBN", book.ISBN);
                parameters.Add("@Title", book.Title);
                parameters.Add("@Author", book.Author);
                parameters.Add("@Publisher", book.Publisher);
                parameters.Add("@PublishDate", book.PublishDate);
                parameters.Add("@CategoryId", book.CategoryId);
                parameters.Add("@Price", book.Price);
                parameters.Add("@Pages", book.Pages);
                parameters.Add("@Description", book.Description);
                parameters.Add("@CoverImage", book.CoverImage);
                parameters.Add("@Location", book.Location);
                parameters.Add("@Status", book.Status);
                parameters.Add("@Id", book.Id);

                var result = connection.Execute(sql, parameters);
                return result > 0;
            }
        }

        /// <summary>
        /// 删除图书
        /// </summary>
        public bool DeleteBook(int bookId)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                // 检查是否有借出的副本
                string checkBorrowed = @"
                SELECT COUNT(*) 
                FROM BookItems bi
                JOIN Borrowings b ON bi.Id = b.BookItemId
                WHERE bi.BookId = @BookId AND b.IsReturned = 0";

                int borrowedCount = connection.ExecuteScalar<int>(checkBorrowed, new { BookId = bookId });
                if (borrowedCount > 0)
                {
                    return false; // 有副本被借出，不能删除
                }

                // 删除所有预约
                string deleteReservations = "DELETE FROM Reservations WHERE BookId = @BookId";
                connection.Execute(deleteReservations, new { BookId = bookId });

                // 删除所有副本的借阅记录
                string deleteBorrowings = @"
                DELETE b FROM Borrowings b
                JOIN BookItems bi ON b.BookItemId = bi.Id
                WHERE bi.BookId = @BookId";
                connection.Execute(deleteBorrowings, new { BookId = bookId });

                // 删除所有副本
                string deleteItems = "DELETE FROM BookItems WHERE BookId = @BookId";
                connection.Execute(deleteItems, new { BookId = bookId });

                // 删除图书
                string deleteBook = "DELETE FROM Books WHERE Id = @Id";
                var result = connection.Execute(deleteBook, new { Id = bookId });
                return result > 0;
            }
        }

        #endregion

        #region 图书副本管理

        /// <summary>
        /// 获取图书的所有副本
        /// </summary>
        public List<BookItem> GetBookItems(int bookId)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = "SELECT * FROM BookItems WHERE BookId = @BookId";
                return connection.Query<BookItem>(sql, new { BookId = bookId }).ToList();
            }
        }

        /// <summary>
        /// 添加图书副本
        /// </summary>
        public int AddBookItem(BookItem item)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                INSERT INTO BookItems (BookId, BarCode, Status, AcquisitionDate, Notes)
                VALUES (@BookId, @BarCode, @Status, @AcquisitionDate, @Notes);
                SELECT LAST_INSERT_ID();";

                var id = connection.ExecuteScalar<int>(sql, item);
                return id;
            }
        }

        /// <summary>
        /// 更新图书副本状态
        /// </summary>
        public bool UpdateBookItemStatus(int itemId, string status)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = "UPDATE BookItems SET Status = @Status WHERE Id = @Id";
                var result = connection.Execute(sql, new { Id = itemId, Status = status });
                return result > 0;
            }
        }

        /// <summary>
        /// 删除图书副本
        /// </summary>
        public bool DeleteBookItem(int itemId)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                // 检查副本是否被借出
                string checkBorrowed = @"
                SELECT COUNT(*) FROM Borrowings
                WHERE BookItemId = @BookItemId AND IsReturned = 0";

                int borrowedCount = connection.ExecuteScalar<int>(checkBorrowed, new { BookItemId = itemId });
                if (borrowedCount > 0)
                {
                    return false; // 副本被借出，不能删除
                }

                // 删除借阅记录
                string deleteBorrowings = "DELETE FROM Borrowings WHERE BookItemId = @BookItemId";
                connection.Execute(deleteBorrowings, new { BookItemId = itemId });

                // 删除副本
                string deleteItem = "DELETE FROM BookItems WHERE Id = @Id";
                var result = connection.Execute(deleteItem, new { Id = itemId });
                return result > 0;
            }
        }

        #endregion

        #region 借阅管理

        /// <summary>
        /// 借书
        /// </summary>
        public bool BorrowBook(int bookItemId, int userId, DateTime dueDate)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 检查图书状态
                        string checkStatus = "SELECT Status FROM BookItems WHERE Id = @Id";
                        string status = connection.ExecuteScalar<string>(checkStatus, new { Id = bookItemId }, transaction);

                        if (status != "在馆")
                        {
                            transaction.Rollback();
                            return false;
                        }

                        // 更新图书状态
                        string updateStatus = @"
                        UPDATE BookItems 
                        SET Status = '借出', LastBorrowDate = @BorrowDate 
                        WHERE Id = @Id";
                        
                        connection.Execute(updateStatus, 
                            new { Id = bookItemId, BorrowDate = DateTime.Now }, 
                            transaction);

                        // 创建借阅记录
                        string insertBorrowing = @"
                        INSERT INTO Borrowings (BookItemId, UserId, BorrowDate, DueDate, IsReturned)
                        VALUES (@BookItemId, @UserId, @BorrowDate, @DueDate, 0)";
                        
                        connection.Execute(insertBorrowing, 
                            new { 
                                BookItemId = bookItemId, 
                                UserId = userId, 
                                BorrowDate = DateTime.Now, 
                                DueDate = dueDate 
                            }, 
                            transaction);

                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 还书
        /// </summary>
        public bool ReturnBook(int bookItemId, decimal fineAmount = 0, string notes = null)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 查找未归还的借阅记录
                        string findBorrowing = @"
                        SELECT Id FROM Borrowings 
                        WHERE BookItemId = @BookItemId AND IsReturned = 0
                        ORDER BY BorrowDate DESC LIMIT 1";
                        
                        var borrowingId = connection.ExecuteScalar<int?>(findBorrowing, 
                            new { BookItemId = bookItemId }, 
                            transaction);

                        if (!borrowingId.HasValue)
                        {
                            transaction.Rollback();
                            return false;
                        }

                        // 更新借阅记录
                        string updateBorrowing = @"
                        UPDATE Borrowings 
                        SET ReturnDate = @ReturnDate, 
                            IsReturned = 1, 
                            FineAmount = @FineAmount,
                            Notes = @Notes
                        WHERE Id = @Id";
                        
                        connection.Execute(updateBorrowing, 
                            new { 
                                Id = borrowingId.Value, 
                                ReturnDate = DateTime.Now, 
                                FineAmount = fineAmount,
                                Notes = notes
                            }, 
                            transaction);

                        // 更新图书状态
                        string updateStatus = "UPDATE BookItems SET Status = '在馆' WHERE Id = @Id";
                        connection.Execute(updateStatus, new { Id = bookItemId }, transaction);

                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 获取用户的借阅历史
        /// </summary>
        public List<Borrowing> GetUserBorrowingHistory(int userId)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                SELECT b.*, bi.BarCode, bk.Title, bk.Author
                FROM Borrowings b
                JOIN BookItems bi ON b.BookItemId = bi.Id
                JOIN Books bk ON bi.BookId = bk.Id
                WHERE b.UserId = @UserId
                ORDER BY b.BorrowDate DESC";

                var borrowings = connection.Query<Borrowing, BookItem, Book, Borrowing>(
                    sql,
                    (borrowing, bookItem, book) =>
                    {
                        bookItem.Book = book;
                        borrowing.BookItem = bookItem;
                        return borrowing;
                    },
                    new { UserId = userId },
                    splitOn: "BarCode,Title"
                ).ToList();

                return borrowings;
            }
        }

        /// <summary>
        /// 获取当前借出的图书
        /// </summary>
        public List<Borrowing> GetCurrentBorrowings()
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                SELECT b.*, u.Username, u.Name, bi.BarCode, bk.Title, bk.Author
                FROM Borrowings b
                JOIN Users u ON b.UserId = u.Id
                JOIN BookItems bi ON b.BookItemId = bi.Id
                JOIN Books bk ON bi.BookId = bk.Id
                WHERE b.IsReturned = 0
                ORDER BY b.DueDate";

                var borrowings = connection.Query<Borrowing, User, BookItem, Book, Borrowing>(
                    sql,
                    (borrowing, user, bookItem, book) =>
                    {
                        borrowing.User = user;
                        bookItem.Book = book;
                        borrowing.BookItem = bookItem;
                        return borrowing;
                    },
                    splitOn: "Username,BarCode,Title"
                ).ToList();

                return borrowings;
            }
        }

        /// <summary>
        /// 获取逾期未还的图书
        /// </summary>
        public List<Borrowing> GetOverdueBorrowings()
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                SELECT b.*, u.Username, u.Name, bi.BarCode, bk.Title, bk.Author
                FROM Borrowings b
                JOIN Users u ON b.UserId = u.Id
                JOIN BookItems bi ON b.BookItemId = bi.Id
                JOIN Books bk ON bi.BookId = bk.Id
                WHERE b.IsReturned = 0 AND b.DueDate < @CurrentDate
                ORDER BY b.DueDate";

                var borrowings = connection.Query<Borrowing, User, BookItem, Book, Borrowing>(
                    sql,
                    (borrowing, user, bookItem, book) =>
                    {
                        borrowing.User = user;
                        bookItem.Book = book;
                        borrowing.BookItem = bookItem;
                        return borrowing;
                    },
                    new { CurrentDate = DateTime.Now },
                    splitOn: "Username,BarCode,Title"
                ).ToList();

                return borrowings;
            }
        }

        #endregion

        #region 预约管理

        /// <summary>
        /// 预约图书
        /// </summary>
        public bool ReserveBook(int bookId, int userId, DateTime expiryDate)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                // 检查是否已有相同预约
                string checkExisting = @"
                SELECT COUNT(*) FROM Reservations
                WHERE BookId = @BookId AND UserId = @UserId AND Status = '等待'";
                
                int count = connection.ExecuteScalar<int>(checkExisting, 
                    new { BookId = bookId, UserId = userId });
                
                if (count > 0)
                {
                    return false; // 已存在预约
                }

                string sql = @"
                INSERT INTO Reservations (BookId, UserId, ReservationDate, ExpiryDate, Status)
                VALUES (@BookId, @UserId, @ReservationDate, @ExpiryDate, '等待')";
                
                var result = connection.Execute(sql, new
                {
                    BookId = bookId,
                    UserId = userId,
                    ReservationDate = DateTime.Now,
                    ExpiryDate = expiryDate
                });
                
                return result > 0;
            }
        }

        /// <summary>
        /// 取消预约
        /// </summary>
        public bool CancelReservation(int reservationId)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = "UPDATE Reservations SET Status = '已取消' WHERE Id = @Id";
                var result = connection.Execute(sql, new { Id = reservationId });
                return result > 0;
            }
        }

        /// <summary>
        /// 完成预约
        /// </summary>
        public bool CompleteReservation(int reservationId)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = "UPDATE Reservations SET Status = '已完成' WHERE Id = @Id";
                var result = connection.Execute(sql, new { Id = reservationId });
                return result > 0;
            }
        }

        /// <summary>
        /// 获取用户的预约
        /// </summary>
        public List<Reservation> GetUserReservations(int userId)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                SELECT r.*, b.Title, b.Author
                FROM Reservations r
                JOIN Books b ON r.BookId = b.Id
                WHERE r.UserId = @UserId
                ORDER BY r.ReservationDate DESC";

                var reservations = connection.Query<Reservation, Book, Reservation>(
                    sql,
                    (reservation, book) =>
                    {
                        reservation.Book = book;
                        return reservation;
                    },
                    new { UserId = userId },
                    splitOn: "Title"
                ).ToList();

                return reservations;
            }
        }

        /// <summary>
        /// 获取当前所有等待中的预约
        /// </summary>
        public List<Reservation> GetActiveReservations()
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                SELECT r.*, u.Username, u.Name, b.Title, b.Author
                FROM Reservations r
                JOIN Users u ON r.UserId = u.Id
                JOIN Books b ON r.BookId = b.Id
                WHERE r.Status = '等待'
                ORDER BY r.ReservationDate";

                var reservations = connection.Query<Reservation, User, Book, Reservation>(
                    sql,
                    (reservation, user, book) =>
                    {
                        reservation.User = user;
                        reservation.Book = book;
                        return reservation;
                    },
                    splitOn: "Username,Title"
                ).ToList();

                return reservations;
            }
        }

        #endregion

        #region 辅助方法

        /// <summary>
        /// 获取图书信息
        /// </summary>
        public Book GetBookById(int id)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = @"
                    SELECT b.*, c.CategoryName, c.CategoryCode, c.Description as CategoryDescription
                    FROM Books b
                    LEFT JOIN BookCategories c ON b.CategoryId = c.Id
                    WHERE b.Id = @Id";

                Book book = null;
                using (var reader = connection.ExecuteReader(sql, new { Id = id }))
                {
                    if (reader.Read())
                    {
                        book = new Book
                        {
                            Id = Convert.ToInt32(reader["Id"]),
                            ISBN = reader["ISBN"].ToString(),
                            Title = reader["Title"].ToString(),
                            Author = reader["Author"].ToString(),
                            Publisher = reader["Publisher"].ToString(),
                            PublishDate = reader["PublishDate"] == DBNull.Value ? null : (DateTime?)reader["PublishDate"],
                            CategoryId = Convert.ToInt32(reader["CategoryId"]),
                            Price = reader["Price"] == DBNull.Value ? null : (decimal?)reader["Price"],
                            Pages = reader["Pages"] == DBNull.Value ? null : (int?)Convert.ToInt32(reader["Pages"]),
                            Description = reader["Description"] == DBNull.Value ? null : reader["Description"].ToString(),
                            CoverImage = reader["CoverImage"] == DBNull.Value ? null : reader["CoverImage"].ToString(),
                            Location = reader["Location"] == DBNull.Value ? null : reader["Location"].ToString(),
                            Status = reader["Status"] == DBNull.Value ? null : reader["Status"].ToString(),
                            CreatedAt = Convert.ToDateTime(reader["CreatedAt"])
                        };

                        if (reader["CategoryId"] != DBNull.Value)
                        {
                            book.Category = new BookCategory
                            {
                                Id = Convert.ToInt32(reader["CategoryId"]),
                                CategoryName = reader["CategoryName"].ToString(),
                                CategoryCode = reader["CategoryCode"].ToString(),
                                Description = reader["CategoryDescription"] == DBNull.Value ? null : reader["CategoryDescription"].ToString()
                            };
                        }
                    }
                }

                if (book != null)
                {
                    // 获取图书的副本信息
                    book.Items = GetBookItems(book.Id);
                }

                return book;
            }
        }

        /// <summary>
        /// 检查ISBN是否已存在
        /// </summary>
        /// <param name="isbn">ISBN码</param>
        /// <param name="bookId">当前图书ID（编辑模式下使用）</param>
        /// <returns>如果ISBN已存在返回true，否则返回false</returns>
        public bool IsIsbnExists(string isbn, int bookId = 0)
        {
            using (var connection = DatabaseConfig.GetConnection())
            {
                string sql = "SELECT COUNT(*) FROM Books WHERE ISBN = @ISBN";
                
                // 如果是编辑模式，排除当前图书ID
                if (bookId > 0)
                {
                    sql += " AND Id != @BookId";
                }
                
                var parameters = new DynamicParameters();
                parameters.Add("@ISBN", isbn);
                
                if (bookId > 0)
                {
                    parameters.Add("@BookId", bookId);
                }
                
                var count = connection.ExecuteScalar<int>(sql, parameters);
                return count > 0;
            }
        }

        #endregion
    }
} 